package com.kaifamiao.xml.d0929;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class Demo {

    public static void main(String[] args) {
        InputStream inputStream = Demo.class.getClassLoader().getResourceAsStream("stu.xml");
        List<Student> list = parseXML(inputStream);
        System.out.println(insert(list));
    }

    private static String url;
    private static String user;
    private static String password;

    static {
        InputStream inputStream = Demo.class.getClassLoader().getResourceAsStream("db.properties");
        Properties prop = new Properties();
        try {
            prop.load(inputStream);
            url = prop.getProperty("url");
            user = prop.getProperty("user");
            password = prop.getProperty("password");
            Class.forName(prop.getProperty("driver"));
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static int insert(List<Student> list){
        int count = 0;
        String sql = "insert into student(`id`, `name`, `nick`, `gender`, `age`) values(?, ?, ?, ?, ?)";
        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = connection.prepareStatement(sql)
        ) {
            for (Student student : list){
                preparedStatement.setObject(1, student.getId());
                preparedStatement.setObject(2, student.getName());
                preparedStatement.setObject(3, student.getNick());
                preparedStatement.setObject(4, student.getGender());
                preparedStatement.setObject(5, student.getAge());

                count += preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }



    public static List<Student> parseXML(InputStream in){
        SAXReader reader = new SAXReader();

        try {
            Document document = reader.read(in);

            Element kfm = document.getRootElement();

            Element students = kfm.element("students");

            List<Element> list = students.elements("student");

//            System.out.println("编号\t姓名\t\t昵称\t性别\t年龄");

            List<Student> studentList = new ArrayList<>();
            for (Element student: list){
//                Node node = student.selectSingleNode("id");
                Element id = student.element("id");
                Element name = student.element("name");
                Element gender = student.element("gender");
                Element age = student.element("age");
                String nick = name.attribute("nick").getValue();

                Student stu = new Student();
                stu.setId(Integer.parseInt(id.getTextTrim()));
                stu.setName(name.getTextTrim());
                stu.setNick(nick);
                stu.setGender(gender.getTextTrim());
                stu.setAge(Integer.parseInt(age.getTextTrim()));

                studentList.add(stu);
//                System.out.println(no.getTextTrim() + "\t" + name.getTextTrim()
//                        + "\t\t" +  + "\t" + gender.getTextTrim()
//                        + "\t" + age.getTextTrim());
            }
            return studentList;
        } catch (DocumentException e) {
            e.printStackTrace();
        }
        return null;
    }
}
